﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
namespace DAL
{
    public class DataService
    {
        public string ChuoiKetNoi
        {
            get
            {
                return EnvInfo.ConnectionString;
            }
        }      
        public SqlConnection TaoKetNoi()
        {
            return new SqlConnection(ChuoiKetNoi);
        }
        public DataTable ViewTableParam(string sql, params SqlParameter[] lstparam)
        {
            SqlDataAdapter da = new SqlDataAdapter(sql,ChuoiKetNoi);
            da.SelectCommand.Parameters.AddRange(lstparam);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public DataTable ViewTableParamStore(string sql, params SqlParameter[] lstparam)
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, ChuoiKetNoi);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddRange(lstparam);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public DataTable ViewTableSql(string sql)
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, ChuoiKetNoi);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public void ExeCuteStoreParam(string sql, params SqlParameter[] lstparam)
        {
            SqlConnection KetNoi = TaoKetNoi();
            try
            {
                if (KetNoi.State == ConnectionState.Closed)
                    KetNoi.Open();
                SqlCommand cmd = new SqlCommand(sql, KetNoi);
                cmd.Parameters.AddRange(lstparam);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                KetNoi.Close();
            }
        }
        public string ExeCuteScalarParamStore(string sql, params SqlParameter[] lstparam)
        {
            SqlConnection KetNoi = TaoKetNoi();
            string Values = string.Empty; ;
            try
            {
                if (KetNoi.State == ConnectionState.Closed)
                    KetNoi.Open();
                SqlCommand cmd = new SqlCommand(sql, KetNoi);
                cmd.Parameters.AddRange(lstparam);
                cmd.CommandType = CommandType.StoredProcedure;
                Values = cmd.ExecuteScalar().ToString();
                return Values;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                KetNoi.Close();
            }

        }
        public string ExeCuteScalarParam(string sql, params SqlParameter[] lstparam)
        {
            SqlConnection KetNoi = TaoKetNoi();
            string Values = string.Empty; ;
            try
            {
                if (KetNoi.State == ConnectionState.Closed)
                    KetNoi.Open();
                SqlCommand cmd = new SqlCommand(sql, KetNoi);
                cmd.Parameters.AddRange(lstparam);
                cmd.CommandType = CommandType.Text;
                Values = cmd.ExecuteScalar().ToString();
                return Values;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                KetNoi.Close();
            }

        }
        public void ExeCuteSqlParam(string sql, params SqlParameter[] lstparam)
        {
            SqlConnection KetNoi = TaoKetNoi();
            try
            {
                if (KetNoi.State == ConnectionState.Closed)
                    KetNoi.Open();
                SqlCommand cmd = new SqlCommand(sql, KetNoi);
                cmd.Parameters.AddRange(lstparam);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally {
                KetNoi.Close();
            }
        }
        public SqlDataReader ViewReaderStore(string sql, params SqlParameter[] lstparam)
        {
            SqlConnection KetNoi = TaoKetNoi();
            KetNoi.Open();
            SqlCommand cmd = new SqlCommand(sql, KetNoi);
            cmd.Parameters.AddRange(lstparam);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }
        public SqlDataReader ViewReaderSqlParam(string sql, params SqlParameter[] lstparam)
        {
            SqlConnection KetNoi = TaoKetNoi();
            KetNoi.Open();
            SqlCommand cmd = new SqlCommand(sql, KetNoi);
            cmd.Parameters.AddRange(lstparam);
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }
        public SqlDataReader ViewReaderSql(string sql)
        {
            SqlConnection KetNoi = TaoKetNoi();
            KetNoi.Open();
            SqlCommand cmd = new SqlCommand(sql, KetNoi);
            cmd.CommandType = CommandType.Text;
            SqlDataReader dr = cmd.ExecuteReader();
            return dr;
        }
    }    
}
